from flask import jsonify, request
from sqlalchemy import func
from sqlalchemy.exc import IntegrityError

from tzq.database_compare_system import dcs_bp
from tzq.database_compare_system.models.dcs_task_models import DatabaseInfo
from tzq.extensions import db


# 获取数据接口 (Layui表格格式)
@dcs_bp.route('/api/databaseInfos', methods=['GET'])
def get_database_infos():
    page = int(request.args.get('page', 1))
    limit = int(request.args.get('limit', 10))

    # 获取搜索参数
    connect_name = request.args.get("connect_name")
    host = request.values.get("host")
    database_type = request.values.get("database_type")
    username = request.values.get("username")

    # 基础查询
    query = DatabaseInfo.query

    # 应用搜索条件
    if connect_name:
        query = query.filter(DatabaseInfo.connect_name.ilike(f'%{connect_name}%'))
    if host:
        query = query.filter(DatabaseInfo.host.ilike(f'%{host}%'))
    if database_type:
        query = query.filter(DatabaseInfo.database_type.ilike(f'%{database_type}%'))
    if username:
        query = query.filter(DatabaseInfo.username.ilike(f'%{username}%'))
    # query = query.filter(DatabaseInfo.connect_name.ilike(f'%{connect_name}%')
    #                      , DatabaseInfo.host.ilike(f'%{host}%'))

    # 获取排序参数
    sort_field = request.args.get('field', 'last_update_date')
    sort_order = request.args.get('order', 'desc')

    # 构建排序
    if hasattr(DatabaseInfo, sort_field):
        if sort_order == 'asc':
            order_by = getattr(DatabaseInfo, sort_field).asc()
        else:
            order_by = getattr(DatabaseInfo, sort_field).desc()
        query = query.order_by(order_by)
    else:
        query = query.order_by(DatabaseInfo.last_update_date.desc())

    # 分页查询
    pagination = query.paginate(page=page, per_page=limit, error_out=False)

    # page = User.query.filter(User.status == 1).order_by(User.id.desc()).paginate(page=1, per_page=10)

    # 构建Layui要求的JSON格式
    return jsonify({
        'code': 0,
        'msg': '',
        'count': pagination.total,
        'data': [databaseInfo.to_dict() for databaseInfo in pagination.items]
    })


# 添加数据接口
@dcs_bp.route('/api/databaseInfo', methods=['POST'])
def add_database_info():
    data = request.get_json()
    try:
        databaseInfo = DatabaseInfo(
            connect_name=data['connect_name'],
            database_type=data['database_type'],
            host=data['host'],
            port=int(data['port']),
            dbname=data['dbname'],
            username=data['username'],
            password=data['password'],
            comment=data['comment']
            # price=float(data['database_type']),
        )
        db.session.add(databaseInfo)
        db.session.commit()
        return jsonify({"code": 0, "msg": "添加成功"})
    except IntegrityError:
        return jsonify({"code": 1, "msg": "数据已存在"}), 400
    except Exception as e:
        return jsonify({"code": 2, "msg": str(e)}), 500


# 更新数据接口
@dcs_bp.route('/api/databaseInfo/<int:id>', methods=['PUT'])
def update_database_info(id):
    databaseInfo = DatabaseInfo.query.get_or_404(id)
    data = request.get_json()
    try:
        databaseInfo.connect_name = data['connect_name']
        databaseInfo.database_type = data['database_type']
        databaseInfo.host = data['host']
        databaseInfo.port = int(data['port'])
        databaseInfo.dbname = data['dbname']
        databaseInfo.username = data['username']
        databaseInfo.password = data['password']
        databaseInfo.comment = data['comment']
        databaseInfo.last_update_date = func.now()
        db.session.commit()
        return jsonify({"code": 0, "msg": "更新成功"})
    except Exception as e:
        return jsonify({"code": 1, "msg": str(e)}), 500


# layui的table的单元格编辑，保存到后台
@dcs_bp.route('/api/databaseInfoUpdate', methods=['POST'])
def update_cell():
    data = request.json
    database_info = DatabaseInfo.query.get(data['id'])
    if not database_info:
        return jsonify({'status': 'error', 'message': 'Database Info not found'}), 404

    # 更新对应字段
    if data['field'] == 'dbname':
        database_info.dbname = data['value']
        database_info.last_update_date = func.now()
    elif data['field'] == 'username':
        database_info.username = float(data['value'])
        database_info.last_update_date = func.now()
    elif data['field'] == 'comment':
        database_info.comment = data['value']
        database_info.last_update_date = func.now()

    try:
        db.session.commit()
        return jsonify({'status': 'success'})
    except Exception as e:
        db.session.rollback()
        return jsonify({'status': 'error', 'message': str(e)}), 500


# 删除数据接口
@dcs_bp.route('/api/databaseInfo/<int:id>', methods=['DELETE'])
def delete_database_info(id):
    databaseInfo = DatabaseInfo.query.get_or_404(id)
    try:
        db.session.delete(databaseInfo)
        db.session.commit()
        return jsonify({"code": 0, "msg": "删除成功"})
    except Exception as e:
        return jsonify({"code": 1, "msg": str(e)}), 500


@dcs_bp.route('/api/getConnectNames', methods=['GET'])
def get_connect_names():
    """
    “配置比对任务”查询窗口 的 添加、编辑页面 ， “数据源”下拉框 展示
    """
    try:
        # 查询 delete_flag='N' 的 connect_name 去重结果
        results = db.session.query(DatabaseInfo.db_info_id, DatabaseInfo.connect_name).filter(
            DatabaseInfo.delete_flag == 'N').distinct().all()

        # 提取结果中的 connect_name # 提取结果（因为返回的是元组列表）
        connect_names = [{"db_info_id": result[0], "connect_name": result[1]} for result in results]
        # print("connect_names = " + str(connect_names))
        return jsonify({"code": 0, "msg": "", "count": len(connect_names), "data": connect_names})
    except Exception as e:
        print("Database error:", e)
        return jsonify({"code": -1, "msg": str(e), "count": 0, "data": None})
